#创建对应目录
mkdir -p ./init/{master,slave}
#将master初始化脚本写入到init/master目录下
cat > ./init/master/create_sync_user.sh <<EOF
#!/bin/bash
#定义用于同步的用户名
MASTER_SYNC_USER=\${MASTER_SYNC_USER:-sync_admin}
#定义用于同步的用户密码
MASTER_SYNC_PASSWORD=\${MASTER_SYNC_PASSWORD:-123456}
#定义用于登录mysql的用户名
ADMIN_USER=\${ADMIN_USER:-root}
#定义用于登录mysql的用户密码
ADMIN_PASSWORD=\${ADMIN_PASSWORD:-123456}
#定义运行登录的host地址
ALLOW_HOST=\${ALLOW_HOST:-%}
#定义创建账号的sql语句
CREATE_USER_SQL="CREATE USER '\$MASTER_SYNC_USER'@'\$ALLOW_HOST' IDENTIFIED BY '\$MASTER_SYNC_PASSWORD';"
#定义赋予同步账号权限的sql,这里设置两个权限，REPLICATION SLAVE，属于从节点副本的权限，REPLICATION CLIENT是副本客户端的权限，可以执行show master status语句
GRANT_PRIVILEGES_SQL="GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '\$MASTER_SYNC_USER'@'\$ALLOW_HOST';"
#定义刷新权限的sql
FLUSH_PRIVILEGES_SQL="FLUSH PRIVILEGES;"
#执行sql
mysql -u"\$ADMIN_USER" -p"\$ADMIN_PASSWORD" -e "\$CREATE_USER_SQL \$GRANT_PRIVILEGES_SQL \$FLUSH_PRIVILEGES_SQL"
EOF


mkdir -p ./init/slave
cat > ./init/slave/slave.sh <<EOF
  #定义连接master进行同步的账号
  SLAVE_SYNC_USER="\${SLAVE_SYNC_USER:-sync_admin}"
  #定义连接master进行同步的账号密码
  SLAVE_SYNC_PASSWORD="\${SLAVE_SYNC_PASSWORD:-123456}"
  #定义slave数据库账号
  ADMIN_USER="\${ADMIN_USER:-root}"
  #定义slave数据库密码
  ADMIN_PASSWORD="\${ADMIN_PASSWORD:-123456}"
  #定义连接master数据库host地址
  MASTER_HOST="\${MASTER_HOST:-%}"
  #等待10s，保证master数据库启动成功，不然会连接失败
  sleep 10
  #连接master数据库，查询二进制数据，并解析出logfile和pos，这里同步用户要开启 REPLICATION CLIENT权限，才能使用SHOW MASTER STATUS;
  RESULT=\`mysql -u"\$SLAVE_SYNC_USER" -h\$MASTER_HOST -p"\$SLAVE_SYNC_PASSWORD" -e "SHOW MASTER STATUS;" | grep -v grep |tail -n +2| awk '{print \$1,\$2}'\`
  #解析出logfile
  LOG_FILE_NAME=\`echo \$RESULT | grep -v grep | awk '{print \$1}'\`
  #解析出pos
  LOG_FILE_POS=\`echo \$RESULT | grep -v grep | awk '{print \$2}'\`
  #设置连接master的同步相关信息
  SYNC_SQL="change master to master_host='\$MASTER_HOST',master_user='\$SLAVE_SYNC_USER',master_password='\$SLAVE_SYNC_PASSWORD',master_log_file='\$LOG_FILE_NAME',master_log_pos=\$LOG_FILE_POS;"
  #开启同步
  START_SYNC_SQL="start slave;"
  #查看同步状态
  STATUS_SQL="show slave status\G;"
  mysql -u"\$ADMIN_USER" -p"\$ADMIN_PASSWORD" -e "\$SYNC_SQL \$START_SYNC_SQL \$STATUS_SQL"
EOF



#创建网卡，保证master和slave都在同一个网段
docker network create --driver=bridge --subnet=10.10.0.0/16 mysql
#启动mysql-master
docker run --name mysql-master
-v ${PWD}/init/master:/docker-entrypoint-initdb.d \
-e MYSQL_ROOT_PASSWORD="123456" \
-e MASTER_SYNC_USER="sync_root" \
-e MASTER_SYNC_PASSWORD="sync_123456" \
-e ADMIN_USER="root" \
-e ADMIN_PASSWORD="123456" \
-e ALLOW_HOST="%" \
-e TZ="Asia/Shanghai" \
--network mysql -d mysql:5.7 \
--server-id=1 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --log-bin=mysql-bin --sync_binlog=1


#启动mysql-slave
docker run --name mysql-slave --network mysql -v ${PWD}/init/slave:/docker-entrypoint-initdb.d \
-e SLAVE_SYNC_USER="sync_root" \
-e SLAVE_SYNC_PASSWORD="sync_123456" \
-e MASTER_HOST="10.10.10.10" \
-e MYSQL_ROOT_PASSWORD="123456" \
-e ADMIN_USER="root" \
-e ADMIN_PASSWORD="123456" \
-d mysql:5.7 --server-id=2 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci





#查看同步状态
#1、登录master查看
sudo docker exec -it mysql-master bash
mysql -uroot -p123456
#查看状态:
show master status \G

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql>

#2、登录slave1查看
sudo docker exec -it mysql-slave1 bash
mysql -uroot -p123456
#查看主从同步状态：
show slave status \G
#可以看到 Slave_IO_Running 和 Slave_SQL_Running 都是Yes，就说明主从同步已经配置成功。
#如果Slave_IO_Running为Connecting，SlaveSQLRunning为Yes，
#则说明配置有问题，这时候就要检查配置中哪一步出现问题了哦，可根据Last_IO_Error字段信息排错。

mysql>
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysqlmaster
                  Master_User: sync_admin
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysqlslave1-relay-bin.000004
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 533
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 9b4f1d60-663d-11ec-86ce-0242ac180002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql>

#3、登录slave2查看
 sudo docker exec -it mysql-slave2 bash
 mysql -uroot -p123456
#查看slave2主从状态：
show slave status \G




#测试数据同步
#1、登录master创建数据库
docker exec -ti mysql-master bash
mysql -uroot -p123456
CREATE DATABASE `sync-data`;
USE `sync-data`;
CREATE TABLE `sync-data`(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '名称'
) ENGINE=INNODB;
INSERT INTO `sync-data`(`name`) VALUES('zhangsan'),('lisi');


#2.进入mysql-slave1，查看数据同步情况
docker exec -ti mysql-slave1 bash
mysql -uroot -p123456
show databases;
SELECT * FROM `sync-data`.`sync-data`;

#3.进入mysql-slave2，查看数据同步情况
docker exec -ti mysql-slave2 bash
mysql -uroot -p123456
show databases;
SELECT * FROM `sync-data`.`sync-data`;